if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MapAgreementChangesFromInfoEd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MapAgreementChangesFromInfoEd]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/***************************************************************************
* Name:			MapAgreementChangesFromInfoEd
* Purpose:		Updates the Insight Agreement data with changes from InfoEd.
*
***************************************************************************/
CREATE PROCEDURE dbo.MapAgreementChangesFromInfoEd
AS
BEGIN

	SET NOCOUNT ON

	BEGIN
		BEGIN TRY
		BEGIN TRANSACTION UpdateAgreementsFromInfoEd

		--OverallStatus
		Update AgrAgreement Set OverallStatus = Stg.OverallStatus
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.OverallStatus, '')!= ISNULL(Stg.OverallStatus, '')

		--Update the propNumber
--		Update AgrAgreement Set PropNumber = Stg.Prop_no
--			From Stg_IE_AgrAgreement Stg
--			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.PropNumber, '')!= ISNULL(Stg.Prop_no, '')

		--Update the DocumentNumber
		Update AgrAgreement Set DocumentNumber = Stg.DocumentNumber
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.DocumentNumber, '')!= ISNULL(Stg.DocumentNumber, '')

		-- PIPerson
		Update AgrAgreement Set PIPersonId = Stg.PIPersonId, PIUniqueId = stg.PIFacultyId
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND
				AgrAgreement.PIPersonId != Stg.PIPersonId
		
		Update AgrAgreement Set ProjectStartDate = Stg.ProjectStartDate
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.ProjectStartDate, '') != ISNULL(Stg.ProjectStartDate, '')
		
		Update AgrAgreement Set ProjectEndDate = Stg.ProjectEndDate
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.ProjectEndDate, '') != ISNULL(Stg.ProjectEndDate, '')
		
		Update AgrAgreement Set Title = Stg.AgreementTitle
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND CAST(AgrAgreement.Title as Varchar(4000)) != Stg.AgreementTitle
		
		Update AgrAgreement Set ShortTitle = Stg.AgreementShortTitle
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND AgrAgreement.ShortTitle != Stg.AgreementShortTitle
		
		Update AgrAgreement Set ReceivedDate = Stg.ReceivedDate
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.ReceivedDate, '') != ISNULL(Stg.ReceivedDate, '')

		Update AgrAgreement Set LegacyIdentifier = Stg.LegacyIdentifier
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.LegacyIdentifier, '') != ISNULL(Stg.LegacyIdentifier, '')

		Update AgrAgreement Set ParentFolderNumber = Stg.ParentFolderNumber
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.ParentFolderNumber, '') != ISNULL(Stg.ParentFolderNumber, '') 
		
		Update AgrAgreement Set RFAID = Stg.RFAID
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.RFAID, '') != ISNULL(Stg.RFAID, '')

		Update AgrAgreement Set RFATitle = Stg.RFATitle
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.RFATitle, '')!= ISNULL(Stg.RFATitle, '')

		Update AgrAgreement Set AgrAgreementStatusTypeId = Stg.AgrAgreementStatusTypeId
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId 

		UPDATE AgrAgreement Set PIUniqueId = Stg.PIFacultyId
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId and isnull(AgrAgreement.PIUniqueId,'') <> Stg.PIFacultyId

	-- Agreement Type.
		UPDATE AgrAgreement Set AgrAgreementTypeId = Stg.AgrAgreementTypeId
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId and isnull(AgrAgreement.AgrAgreementTypeId,0) <> Stg.AgrAgreementTypeId

	-- Sponsor Map!
		INSERT Into SvcEntity_AdmSponsor_Map 
				(SvcEntityId, AdmSponsorId, AdmSponsorRelationTypeId, DateCreated, SponsorNumber )
		SELECT	a.SvcEntityId, s.AdmSponsorId, 1, GETDATE(), s.SponsorNumber 
		FROM Stg_IE_AgrAgreement s
		inner join agragreement a on a.Id = s.AgrAgreementId  --a.foldernumber = s.foldernumber and a.iscurrent=1 and a.isamendment=0
--						and a.documentnumber = (select max(a1.documentnumber) from agragreement a1
--												where a1.foldernumber =  a.foldernumber 
--													and a1.iscurrent=1 and a1.isamendment=0)
		LEFT OUTER JOIN SvcEntity_AdmSponsor_Map m 
			on m.svcentityid = a.SvcEntityId and m.AdmSponsorRelationTypeId = 1
		WHERE s.AdmSponsorId IS NOT NULL AND a.SvcEntityId is not null and m.svcentityid is null

		UPDATE SvcEntity_AdmSponsor_Map SET AdmSponsorId = Stg.AdmSponsorId
			FROM Stg_IE_AgrAgreement Stg
			WHERE SvcEntity_AdmSponsor_Map.SvcEntityId = Stg.SvcEntityId
				AND		SvcEntity_AdmSponsor_Map.AdmSponsorId != Stg.AdmSponsorId
				AND AdmSponsorRelationTypeId = 1

		UPDATE 	SvcEntity_AdmSponsor_Map SET SponsorNumber = Stg.SponsorNumber	
				FROM Stg_IE_AgrAgreement Stg
				inner join AgrAGreement A
					on Stg.AgrAgreementId = A.Id
				inner join SvcEntity_AdmSponsor_Map SM
					on SM.SVcEntityId = A.SvcEntityId

--		Update AgrAgreement Set AdmSponsorId = Stg.AdmSponsorId
--			From Stg_IE_AgrAgreement Stg
--			Where AgrAgreement.Id = Stg.AgrAgreementId 

		Update AgrAgreement Set PIPersonId = Stg.PIPersonId
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId --AND AgrAgreement.PIPersonId != Stg.PIPersonId

		UPDATE AgrAgreement Set IsCurrent = Stg.IsCurrent
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId
			and AgrAgreement.IsCurrent <> Stg.IsCurrent

		UPDATE AgrAgreement Set IsAmendment = Stg.IsAmendment
			From Stg_IE_AgrAgreement Stg
			Where AgrAgreement.Id = Stg.AgrAgreementId
			and AgrAgreement.IsAmendment <> Stg.IsAmendment
		
		-- Activity Type ID
		Update AgrActivity SET AgrActivityTypeId = Stg.AgrActivityTypeID
			From Stg_IE_AgrAgreement Stg
			Where AgrActivity.AgrAgreementId = Stg.AgrAgreementId 
				and Stg.AgrActivityTypeID IS NOT NULL

		-- SUB Type
		Update AgrActivity SET ActivitySubType = Stg.SubActivityType 
			From Stg_IE_AgrAgreement Stg
			Where AgrActivity.AgrAgreementId = Stg.AgrAgreementId 
		
		-- Activity Type Text field
		Update AgrActivity Set ActivityType = Stg.Description
			FROM AgrActivityType Stg
			WHERE Stg.Id = AgrActivity.AgrActivityTypeId

		-- ACtivity Type PSDescription from Research Finance
		UPDATE AgrActivity SET PSDescription = Stg.ActTypePSDescription 
			FROM Stg_IE_AgrAgreement STg
			WHERE AgrActivity.AgrAGreementId = Stg.AgrAgreementID
			AND ISNULL(Stg.ActTypePSDescription, '') != ISNULL(AgrActivity.PSDescription, '')
			AND Stg.ActTypePSDescription IS NOT NULL


--		-- ONE TIME UPDATE		
--		Update AgrAgreement Set PropNumber = Stg.Prop_No
--			From Stg_IE_AgrAgreement Stg
--			Where AgrAgreement.Id = Stg.AgrAgreementId AND ISNULL(AgrAgreement.PropNumber, '') != Stg.Prop_No
--		

	----------------------------------------------------------------------
	-- COMMIT THE TRANSACTION!!!
	-- And end the try block, then handle any errors.
	----------------------------------------------------------------------
	COMMIT TRANSACTION UpdateAgreementsFromInfoEd
	END TRY

	BEGIN CATCH
		DECLARE 
			@ErrorMessage    NVARCHAR(4000),
			@ErrorNumber     INT,
			@ErrorSeverity   INT,
			@ErrorState      INT,
			@ErrorLine       INT,
			@ErrorProcedure  NVARCHAR(200);

		-- Assign variables to error-handling functions that 
		-- capture information for RAISERROR.
		SELECT 
			@ErrorNumber = ERROR_NUMBER(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE(),
			@ErrorLine = ERROR_LINE(),
			@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

		-- Building the message string that will contain original
		-- error information.
		SELECT @ErrorMessage = 
			N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
				'Message: '+ ERROR_MESSAGE();
	
		-----------------------------------------------------
		-- ROLLBACK the transaction if we got here....
		-----------------------------------------------------
		IF XACT_STATE() <> 0 Rollback transaction UpdateAgreementsFromInfoEd

		-- Raise an error: msg_str parameter of RAISERROR will contain
		-- the original error information.
		RAISERROR 
			(
			@ErrorMessage, 
			@ErrorSeverity, 
			1,               
			@ErrorNumber,    -- parameter: original error number.
			@ErrorSeverity,  -- parameter: original error severity.
			@ErrorState,     -- parameter: original error state.
			@ErrorProcedure, -- parameter: original error procedure name.
			@ErrorLine       -- parameter: original error line number.
			);
			return 99
		END CATCH;

	END
		
	SET NOCOUNT OFF

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO